Converting JSON file to CSV or Excel using Alteryx
Introduction:
By understanding the hierarchy, i.e., parent to child relationship, of a JSON file it is easy to convert it to a tabular display with Alteryx and export it to a desired output such as CSV, Excel, or a database.
How to Convert JSON to Excel / CSV:
Consider a JSON file whose preview is as shown below. Using Alteryx, this data can be converted into the tabular format for the ease of data processing.
Let us begin with connecting the file using the Input Data tool. Choose the file format as JSON and enable the option for “Parse Value as String”. The data preview can be seen as below.
Given the data structure, it is most efficient to use a “Text to Columns” tool in order to split the desired column as shown below.
Add a “Cross Tab” tool to the workflow, and configure the settings as shown below. The data would be converted to an easily comprehensible format.
Finally by sorting the data rows and rearranging the columns we can get the desired tabular output as shown below.
Depending on the JSON hierarchy, usage of nested objects, parent to child relationship, etc. there may be additional tools needed to transform the data and achieve the desired results.
Summary:
Using Alteryx, it is possible to convert JSON data into tabular format which can be processed or stored further as Excel, CSV or other databases.
